package agenceVoyageVirtuel.server.base;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import agenceVoyageVirtuel.client.beans.CommandeData;
import agenceVoyageVirtuel.client.beans.EpoqueData;
import agenceVoyageVirtuel.client.beans.VoyageData;
import agenceVoyageVirtuel.server.DB_Conn;

public class DB_Voyage extends DB_Conn {
	/**
	 * constructor - nothing to do
	 */
	public DB_Voyage() {
		// nothing to do
	}
	
	public List<VoyageData> getVoyages(int idCommande) {
		String query = "SELECT t_voyage.idVoyage, destination, reel, tarifVoyage, image, duree, t_epoque.idEpoque, libEpoque"
			+ " FROM t_tour JOIN t_voyage, t_epoque"
			+ " WHERE idCommande=" + idCommande
			+ " AND t_tour.idVoyage=t_voyage.idVoyage"
			+ " AND t_tour.idEpoque=t_epoque.idEpoque;";
		
		List<VoyageData> voyageDatas = new ArrayList<VoyageData>();
	
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				voyageDatas.add(new VoyageData());
				voyageDatas.get(i).setIdVoyage(result.getInt(1));
				voyageDatas.get(i).setDestination(result.getString(2));
				voyageDatas.get(i).setReel(result.getBoolean(3));
				voyageDatas.get(i).setTarifQuotidien(result.getInt(4));
				voyageDatas.get(i).setNomImage(result.getString(5));
				voyageDatas.get(i).setDuree(result.getInt(6));
				//Epoque
				voyageDatas.get(i).setEpoque(new EpoqueData());
				voyageDatas.get(i).getEpoque().setIdEpoque(result.getInt(7));
				voyageDatas.get(i).getEpoque().setLibEpoque(result.getString(8));
				
				i++;
			}
	
			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}
	
		// return datas
		return voyageDatas;			
	}	
	
	public List<VoyageData> getVoyages() {
		String query = "SELECT idVoyage, destination, reel, tarifVoyage, image, duree FROM t_voyage;";

		// prepare for rpc transport
		List<VoyageData> voyageDatas = new ArrayList<VoyageData>();
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				voyageDatas.add(new VoyageData());
				voyageDatas.get(i).setIdVoyage(result.getInt(1));
				voyageDatas.get(i).setDestination(result.getString(2));
				voyageDatas.get(i).setReel(result.getBoolean(3));
				voyageDatas.get(i).setTarifQuotidien(result.getInt(4));
				voyageDatas.get(i).setNomImage(result.getString(5));
				voyageDatas.get(i).setDuree(result.getInt(6));

				i++;
			}

			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

		// return datas
		return voyageDatas;
	}
	
	public void commanderVoyages(CommandeData commandeEnCours) {
		for(VoyageData voyage : commandeEnCours.getVoyages()) {
			String query = "INSERT INTO t_tour (idCommande, idVoyage, idEpoque, duree)"
					+ " VALUES (" + commandeEnCours.getIdCommande()
					+ ", " + voyage.getIdVoyage()
					+ ", " + voyage.getEpoque().getIdEpoque()
					+ ", " + voyage.getDuree()
					+ ");";
		
			try {
				Connection connection = getConn();
				Statement insert = connection.createStatement();
				
	            insert.executeUpdate(query);
	            
	            insert.close();
				connection.close();
			} catch(Exception e) {
				System.err.println("Mysql Statement Error: " + query);
				e.printStackTrace();
			}			
		}
	}
	
	private int chercherVoyage(String nouveauVoyage, String nomImage) {
		String query = "SELECT idSouvenir"
				+ " FROM t_souvenir"
				+ " WHERE destination=" + nouveauVoyage
				+ " OR image=" + nomImage
				+ ";";
	
		// prepare for rpc transport
		int id = -1;
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			
			while(result.next()) {
				id = result.getInt(1); //idVoyage
				break;
			}
	
			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}
	
		// return datas
		return id;		
	}
	
	public void creerVoyage(String nouveauVoyage, boolean reel, int tarifVoyage, String nomImage) {
		int id = chercherVoyage(nouveauVoyage, nomImage);
		
		if(id != -1)
			return;
		
		String query = "INSERT INTO t_souvenir (destination, reel, tarifVoyage, image)"
				+ " VALUES (" + nouveauVoyage
				+ ", " + reel
				+ ", " + tarifVoyage
				+ ", " + nomImage
				+ ");";
	
		try {
			Connection connection = getConn();
			Statement  insert = connection.createStatement();
			insert.executeUpdate(query);
			
	        insert.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql PrepareStatement Error: " + query);
			e.printStackTrace();
		}
		
		return;		
	}		
}
